package hotel.consultas;


import hotel.conexao.Conexao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;



public class ConsultasSQL {
	
	private Connection connection;
	
	
	public ConsultasSQL() throws ClassNotFoundException, IllegalAccessException, SQLException{
		
		connection = Conexao.createConnection();
	
	}
	
	public static void main(String[] args) throws ClassNotFoundException, IllegalAccessException, SQLException {
		
		ConsultasSQL query = new ConsultasSQL();
		query.query15();
		
	}

	
	public void query1() throws ClassNotFoundException, IllegalAccessException, SQLException{
		
        Statement statement = connection.createStatement();
        
        String query = "SELECT s.descricao, SUM(ss.quantidade) AS Quantidade FROM servico_solicitado ss, servico s "
+"WHERE  ss.cod_servico = s.cod_servico AND ROWNUM <=5 GROUP BY s.descricao ORDER BY Quantidade DESC";
        
        ResultSet resultSet = statement.executeQuery(query);
        while (resultSet.next()) {

        	System.out.println(resultSet.getString(1)+" : "+resultSet.getFloat(2));
        	
        	
        }		
		
        statement.close();
        connection.close();
		
	}
public void query2() throws ClassNotFoundException, IllegalAccessException, SQLException{
		
        Statement statement = connection.createStatement();
        
        String query = "SELECT nome, MAX(valor_total) AS Total FROM (SELECT SUM(valor) as valor_total, cli.nome" +
        		" FROM hospedagem h left join cliente cli on h.cod_cliente = cli.cod_cliente group by (cli.nome)) " +
        		"WHERE ROWNUM = 1 GROUP BY nome ORDER BY Total DESC";

        
        ResultSet resultSet = statement.executeQuery(query);
        while (resultSet.next()) {

        	System.out.println(resultSet.getString(1)+" : "+resultSet.getInt(2));
        	
        	
        }		
		
        statement.close();
        connection.close();
		
	}
public void query3() throws ClassNotFoundException, IllegalAccessException, SQLException{
	
    Statement statement = connection.createStatement();
    
    String query = "SELECT c.nome, COUNT(*) AS Quant FROM hospedagem h, cliente c "+
    "WHERE h.cod_cliente = c.cod_cliente and ROWNUM = 1 GROUP BY c.nome ORDER BY Quant DESC";

    
    ResultSet resultSet = statement.executeQuery(query);
    while (resultSet.next()) {

    	System.out.println(resultSet.getString(1)+" : "+resultSet.getInt(2));
    	
    	
    }		
	
    statement.close();
    connection.close();
	
}
public void query4() throws ClassNotFoundException, IllegalAccessException, SQLException{
	
	
    Statement statement = connection.createStatement();
    
    String query = "SELECT dias ,cod_apartamento "+
    		"FROM (SELECT DISTINCT to_date(sysdate) - MAX(to_date(data_saida)) AS dias,a.cod_apartamento FROM hospedagem h, apartamento a WHERE  h.cod_apartamento =a.cod_apartamento GROUP BY a.cod_apartamento) "+
    "WHERE cod_apartamento NOT IN (SELECT DISTINCT a.cod_apartamento FROM hospedagem h, apartamento a WHERE  h.cod_apartamento =a.cod_apartamento AND data_saida IS NULL) ORDER BY dias DESC";

    
    ResultSet resultSet = statement.executeQuery(query);
    while (resultSet.next()) {

    	System.out.println(resultSet.getInt(1)+" : "+resultSet.getInt(2));
    	
    	
    }		
	
    statement.close();
    connection.close();
	
}
public void query5() throws ClassNotFoundException, IllegalAccessException, SQLException{
	
	
    Statement statement = connection.createStatement();
    
    String query = "SELECT sexo, AVG(salario) as M�dia_salarial FROM funcionario GROUP BY sexo";

    
    ResultSet resultSet = statement.executeQuery(query);
    while (resultSet.next()) {

    	System.out.println(resultSet.getString(1)+" : "+resultSet.getFloat(2));
    	
    	
    }		
	
    statement.close();
    connection.close();
	
}
public void query6() throws ClassNotFoundException, IllegalAccessException, SQLException{
	
	
    Statement statement = connection.createStatement();
    
    String query = "SELECT nome, MAX(total) as Total FROM (SELECT c.nome, SUM(s.valor * ss.quantidade) " +
    		"As Total  FROM cliente c INNER JOIN hospedagem hp ON hp.cod_cliente = c.cod_cliente " +
    		"INNER JOIN servico_solicitado ss ON ss.cod_hospedagem = hp.cod_hospedagem " +
            "INNER JOIN servico s ON s.cod_servico = ss.cod_servico " +
            "WHERE EXTRACT(DAY FROM hp.data_saida)-EXTRACT(DAY FROM hp.data_entrada) <= 30 GROUP BY c.nome)" +
           " WHERE ROWNUM = 1 GROUP BY nome";

    
    ResultSet resultSet = statement.executeQuery(query);
    while (resultSet.next()) {

    	System.out.println(resultSet.getString(1)+" : "+resultSet.getFloat(2));
    	
    	
    }		
	
    statement.close();
    connection.close();
	
}
public void query7() throws ClassNotFoundException, IllegalAccessException, SQLException{
	
	
    Statement statement = connection.createStatement();
    
    String query = "SELECT AVG(DIAS) AS MEDIA_DIAS "+
                      "FROM (SELECT DISTINCT to_date(data_saida) - to_date(data_entrada) as dias " +
                      "from servico_solicitado ss left join servico s on ss.cod_servico=s.cod_servico, servico_solicitado ss " +
                      "left join hospedagem h on ss.cod_hospedagem= h.cod_hospedagem Where  s.descricao like '%LAVANDERIA%')";

    
    ResultSet resultSet = statement.executeQuery(query);
    while (resultSet.next()) {

    	System.out.println(resultSet.getFloat(1));
    	
    	
    }		
	
    statement.close();
    connection.close();
	
}
public void query9() throws ClassNotFoundException, IllegalAccessException, SQLException{
	
	
    Statement statement = connection.createStatement();
    
    String query = "SELECT f.razao_social AS Fornecedor, p.descricao As produto , SUM((i.quantidade * p.valor_revenda) - (p.valor_compra * i.quantidade)) As lucro " +
    		          "FROM venda v INNER JOIN itens_venda i ON i.cod_venda = v.cod_venda "+ 
                     "INNER JOIN produto p ON p.cod_produto = i.cod_produto "+
                     "INNER JOIN fornecimento forn ON forn.cod_produto = p.cod_produto "+
                    "INNER JOIN fornecedor f ON f.cod_fornecedor = forn.cod_fornecedor " +
                    "WHERE ROWNUM <= 5 GROUP BY f.razao_social, p.descricao ORDER BY lucro DESC";

    
    ResultSet resultSet = statement.executeQuery(query);
    while (resultSet.next()) {

    	System.out.println(resultSet.getString(1)+" : "+resultSet.getString(2)+" : " + resultSet.getFloat(3));
    	
    	
    }		
	
    statement.close();
    connection.close();
	
}
public void query10() throws ClassNotFoundException, IllegalAccessException, SQLException{
	
	
    Statement statement = connection.createStatement();
    
    String query = "SELECT ap.andar, COUNT(*) AS Clientes_Hospedados " +
    		"FROM apartamento ap, hospedagem h WHERE ap.cod_apartamento = h.cod_apartamento " +
    		"AND ap.status = 'O' AND h.data_saida IS null AND RowNum <= 5 GROUP BY ap.andar " +
    		"ORDER BY Clientes_Hospedados DESC";

    
    ResultSet resultSet = statement.executeQuery(query);
    while (resultSet.next()) {

    	System.out.println("Andar "+resultSet.getInt(1)+" : "+"Quantos Hospedes "+resultSet.getInt(2));
    	
    	
    }		
	
    statement.close();
    connection.close();
	
}
public void query11() throws ClassNotFoundException, IllegalAccessException, SQLException{
	
	
    Statement statement = connection.createStatement();
    
    String query = "SELECT DISTINCT ap.tipo, s.descricao FROM apartamento ap " +
    		"INNER JOIN hospedagem hp ON ap.cod_apartamento = hp.cod_apartamento "	
           + "INNER JOIN servico_solicitado ss ON ss.cod_hospedagem = hp.cod_hospedagem " +
           "INNER JOIN servico s ON s.cod_servico = ss.cod_servico ORDER BY tipo";

    
    ResultSet resultSet = statement.executeQuery(query);
    while (resultSet.next()) {

    	System.out.println("Tipo de Apartamento: "+resultSet.getString(1)+" : "+"Servi�o Solicitado: "+resultSet.getString(2));
    	
    	
    }		
	
    statement.close();
    connection.close();
	
}
public void query12() throws ClassNotFoundException, IllegalAccessException, SQLException{
	
	
    Statement statement = connection.createStatement();
    
    String query = "SELECT DISTINCT f.razao_social, p.descricao, i.quantidade FROM venda v " +
    		"INNER JOIN itens_venda i ON i.cod_venda = v.cod_venda " +
    		"INNER JOIN produto p ON p.cod_produto = i.cod_produto "
            +"INNER JOIN fornecimento forn ON forn.cod_produto = p.cod_produto " +
            "INNER JOIN fornecedor f ON f.cod_fornecedor = forn.cod_fornecedor WHERE ROWNUM <= 5";

    
    ResultSet resultSet = statement.executeQuery(query);
    while (resultSet.next()) {

    	System.out.println(resultSet.getString(1)+" : "+resultSet.getString(2)+" : "+ resultSet.getInt(3));
    	
    	
    }		
	
    statement.close();
    connection.close();
	
}
public void query13() throws ClassNotFoundException, IllegalAccessException, SQLException{
	
	
    Statement statement = connection.createStatement();
    
    String query = "SELECT DISTINCT descricao,data_saida "+
          "from servico_solicitado ss,servico s,  hospedagem h "+
          "where ss.cod_servico=s.cod_servico and ss.cod_hospedagem= h.cod_hospedagem and " +
          "EXTRACT(MONTH FROM SYSDATE)-EXTRACT(MONTH FROM h.data_saida)=6 AND descricao " +
          "NOT IN(SELECT DISTINCT descricao from servico_solicitado ss,servico s,  hospedagem h " +
           "where ss.cod_servico=s.cod_servico and ss.cod_hospedagem= h.cod_hospedagem and " +
          "EXTRACT(MONTH FROM SYSDATE)-EXTRACT(MONTH FROM h.data_saida)<6)";

    
    ResultSet resultSet = statement.executeQuery(query);
    while (resultSet.next()) {

    	System.out.println(resultSet.getString(1)+" : "+resultSet.getDate(2));
    	
    	
    }		
	
    statement.close();
    connection.close();
	
}
public void query14() throws ClassNotFoundException, IllegalAccessException, SQLException{
	
	
    Statement statement = connection.createStatement();
    
    String query = "SELECT  nome, AVG (quantidade_hospedes) AS media_hospedes "+
              "FROM (SELECT COUNT (hp.nome) as quantidade_hospedes,data_entrada,c.nome " +
              "FROM hospedagem h inner join cliente c on h.cod_cliente=c.cod_cliente inner join hospede hp " +
              "ON h.cod_hospedagem= hp.cod_hospedagem group by (c.nome,data_entrada)) GROUP BY nome";

    
    ResultSet resultSet = statement.executeQuery(query);
    while (resultSet.next()) {

    	System.out.println(resultSet.getString(1)+" : "+resultSet.getFloat(2));
    	
    	
    }		
	
    statement.close();
    connection.close();
	
}
public void query15() throws ClassNotFoundException, IllegalAccessException, SQLException{
	
	
    Statement statement = connection.createStatement();
    
    String query = "SELECT descricao FROM (SELECT quant_saida AS Saida, descricao, quantidade " +
    		"AS Estoque, quantidade - quant_saida  AS PROJECAO "
            +"FROM (SELECT i.quantidade As quant_saida, p.descricao, p.quantidade FROM venda v "+
       "JOIN itens_venda i ON i.cod_venda = v.cod_venda "+ 
        "JOIN produto p ON p.cod_produto = i.cod_produto "+
       "WHERE  v.data BETWEEN (SYSDATE - 90 ) AND SYSDATE)) WHERE PROJECAO < 1";

    
    ResultSet resultSet = statement.executeQuery(query);
    while (resultSet.next()) {

    	System.out.println(resultSet.getString(1));
    	
    	
    }		
	
    statement.close();
    connection.close();
	
}
	
}
